package com.cari.oa.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.cari.oa.domain.ReqEntity;
import com.cari.oa.domain.ReqUserRefEntity;
import com.cari.oa.util.StrUtils;

@Repository
public class ReqDao extends BaseDao<ReqEntity> {
	
	/**
	 * 
	* Title: getPage
	* Description:     分页查询需求
	* @author          hjx 
	* @date            2013-3-12 
	* @param title     标题
	* @param initiator 发起人
	* @param status    状态
	* @param reqType   需求类型
	* @param page    
	* @param size    
	* @return
	 */
    public List<ReqEntity> getPage(String title, String enName, String cnName, String status, String reqType,String sortType,String project, int page, int size) {
    	
    	List<Object> params = new ArrayList<Object>();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("from ReqEntity r where 1=1 ");
        if(StrUtils.isNotEmpty(title)) {
            stringBuilder.append("and r.title like ? ");
            params.add("%" + title + "%");
        }
        if(StrUtils.isNotEmpty(project)) {
            stringBuilder.append("and r.project like ? ");
            params.add("%" + project + "%");
        }
        if(StrUtils.isNotEmpty(cnName)) {
            stringBuilder.append("and r.cnName like ? ");
            params.add("%" + cnName + "%");
        }
        if(StrUtils.isNotEmpty(enName)) {
            stringBuilder.append("and r.initiator like ? ");
            params.add("%" + enName + "%");
        }
        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and r.status = ? ");
            params.add(status);
        }
        if(StrUtils.isNotEmpty(reqType)) {
        	stringBuilder.append("and r.reqType = ? ");
        	params.add(reqType);
        }
        if(StrUtils.isNotEmpty(sortType) && sortType.equals("newRepTime"))    //最新回复时间
        {
        	stringBuilder.append(" order by case when  newRepTime is null then '0'" +
        			             " else to_char(newRepTime,'yyyy-mm-dd hh24:mi:ss') end desc");
        }
        else if(StrUtils.isNotEmpty(sortType) && sortType.equals("perStaTime")) //预计开始时间
        {
        	stringBuilder.append(" order by case when  perStaTime is null then '0'" +
        			             " else to_char(perStaTime,'yyyy-mm-dd hh24:mi:ss') end desc ");
        }else
        {
        	stringBuilder.append(" order by createTime desc");
        }
        return getPage(stringBuilder.toString(), page, size, params.toArray());
    }
    
    /**
     * 
    * Title: getCount
    * Description:
    * @author    hjx 
    * @date       2013-3-12 
    * @param title     标题
	* @param initiator 发起人
	* @param status    状态
	* @param reqType   需求类型
    * @return
     */
    public long getCount(String title,String enName, String cnName, String status, String reqType, String sortType,String project) {
    	List<Object> params = new ArrayList<Object>();

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select count(*) from ReqEntity r where 1=1 ");
        
        if(StrUtils.isNotEmpty(title)) {
            stringBuilder.append("and r.title like ? ");
            params.add("%" + title + "%");
        }
        if(StrUtils.isNotEmpty(project)) {
            stringBuilder.append("and r.project like ? ");
            params.add("%" + project + "%");
        }
        if(StrUtils.isNotEmpty(cnName)) {
            stringBuilder.append("and r.cnName like ? ");
            params.add("%" + cnName + "%");
        }
        if(StrUtils.isNotEmpty(enName)) {
            stringBuilder.append("and r.initiator like ? ");
            params.add("%" + enName + "%");
        }
        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and r.status = ? ");
            params.add(status);
        }
        
        if(StrUtils.isNotEmpty(reqType)) {
        	stringBuilder.append("and r.reqType = ? ");
        	params.add(reqType);
        }
        if(StrUtils.isNotEmpty(sortType) && sortType.equals("newRepTime"))    //最新回复时间
        {
        	stringBuilder.append(" order by case when  newRepTime is null then '0'" +
        			             " else to_char(newRepTime,'yyyy-mm-dd hh24:mi:ss') end desc");
        }
        else if(StrUtils.isNotEmpty(sortType) && sortType.equals("perStaTime")) //预计开始时间
        {
        	stringBuilder.append(" order by case when  perStaTime is null then '0'" +
        			             " else to_char(perStaTime,'yyyy-mm-dd hh24:mi:ss') end desc ");
        }else
        {
        	stringBuilder.append(" order by createTime desc");
        }
        Query query = getSession().createQuery(stringBuilder.toString());
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }
        return (Long)query.uniqueResult();
    }

    /**
    * 获取本需求的所有负责人
    *@param reqId
    *@return
    *@author hjx
    *@date 2013-5-10上午11:19:57
    */
	@SuppressWarnings("unchecked")
	public List<ReqUserRefEntity> getAllReqPrincipal(String reqId) {
		List<ReqUserRefEntity> list = null;
		try {
			String hql="select new ReqUserRefEntity(ru.enname , ru.cnname) from ReqEntity r,ReqTypeEntity rt ,ReqUserRefEntity ru " +
				       "where r.reqType = rt.id and rt.id = ru.typeId and r.id ='"+reqId+"'";
			Query query = getSession().createQuery(hql);
			list = query.list();
		} catch (HibernateException e) {
			e.printStackTrace();
		}
		return list;
	}

}
